using GymTimer.Models;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.Logging;

namespace GymTimer.Data
{
    /// <summary>
    /// Repository class for managing Schedules in the database.
    /// </summary>
    public class ScheduleRepository
    {
        private bool _hasBeenInitialized = false;
        private readonly ILogger _logger;
        private readonly ExerciseRepository _exerciseRepository;

  
        public ScheduleRepository(ExerciseRepository exerciseRepository, ILogger<ScheduleRepository> logger)
        {
            _exerciseRepository = exerciseRepository;
            _logger = logger;
        }

        /// <summary>
        /// Initializes the database connection and creates the Schedule table if it does not exist.
        /// </summary>
        private async Task Init()
        {
            if (_hasBeenInitialized)
                return;

            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            try
            {
                var createTableCmd = connection.CreateCommand();
                createTableCmd.CommandText = @"
            CREATE TABLE IF NOT EXISTS Schedule (
                ID INTEGER PRIMARY KEY AUTOINCREMENT,
                Name TEXT NOT NULL,
                PrepareTime INTEGER ,
                Description TEXT ,
                Icon TEXT 
            );";
                await createTableCmd.ExecuteNonQueryAsync();
            }
            catch (Exception e)
            {
                _logger.LogError(e, "Error creating Schedule table");
                throw;
            }

            _hasBeenInitialized = true;
        }

        /// <summary>
        /// Retrieves a list of all Schedules from the database.
        /// </summary>
        /// <returns>A list of <see cref="Schedule"/> objects.</returns>
        public async Task<List<Schedule>> ListAsync()
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var selectCmd = connection.CreateCommand();
            selectCmd.CommandText = "SELECT * FROM Schedule";
            var schedules = new List<Schedule>();

            await using var reader = await selectCmd.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                schedules.Add(new Schedule
                {
                    ID = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    RepareTime = reader.GetInt32(2),
                });
            }

            foreach (var schedule in schedules)
            {
                schedule.Exercises = await _exerciseRepository.ListAsync(schedule.ID);
            }

            return schedules;
        }

        /// <summary>
        /// Retrieves a specific Schedule by its ID.
        /// </summary>
        /// <param name="id">The ID of the Schedule.</param>
        /// <returns>A <see cref="Schedule"/> object if found; otherwise, null.</returns>
        public async Task<Schedule?> GetAsync(int id)
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var selectCmd = connection.CreateCommand();
            selectCmd.CommandText = "SELECT * FROM Schedule WHERE ID = @id";
            selectCmd.Parameters.AddWithValue("@id", id);

            await using var reader = await selectCmd.ExecuteReaderAsync();
            if (await reader.ReadAsync())
            {
                var Schedule = new Schedule
                {
                    ID = reader.GetInt32(0),
                    Name = reader.GetString(1),
                    RepareTime = reader.GetInt32(2),
                };

                Schedule.Exercises = await _exerciseRepository.ListAsync(Schedule.ID);

                return Schedule;
            }

            return null;
        }

        /// <summary>
        /// Saves a Schedule to the database. If the Schedule ID is 0, a new Schedule is created; otherwise, the existing Schedule is updated.
        /// </summary>
        /// <param name="item">The Schedule to save.</param>
        /// <returns>The ID of the saved Schedule.</returns>
        public async Task<int> SaveItemAsync(Schedule item)
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var saveCmd = connection.CreateCommand();
            if (item.ID == 0)
            {
                saveCmd.CommandText = @"
                INSERT INTO Schedule (Name, PrepareTime)
                VALUES (@Name, @PrepareTime);
                SELECT last_insert_rowid();";
            }
            else
            {
                saveCmd.CommandText = @"
                UPDATE Schedule
                SET Name = @Name, PrepareTime = @PrepareTime  
                WHERE ID = @ID";
                saveCmd.Parameters.AddWithValue("@ID", item.ID);
            }

            saveCmd.Parameters.AddWithValue("@Name", item.Name);
            saveCmd.Parameters.AddWithValue("@PrepareTime", item.RepareTime);


            var result = await saveCmd.ExecuteScalarAsync();
            if (item.ID == 0)
            {
                item.ID = Convert.ToInt32(result);
            }

            return item.ID;
        }

        /// <summary>
        /// Deletes a Schedule from the database.
        /// </summary>
        /// <param name="item">The Schedule to delete.</param>
        /// <returns>The number of rows affected.</returns>
        public async Task<int> DeleteItemAsync(Schedule item)
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var deleteCmd = connection.CreateCommand();
            deleteCmd.CommandText = "DELETE FROM Schedule WHERE ID = @ID";
            deleteCmd.Parameters.AddWithValue("@ID", item.ID);

            return await deleteCmd.ExecuteNonQueryAsync();
        }

        /// <summary>
        /// Drops the Schedule table from the database.
        /// </summary>
        public async Task DropTableAsync()
        {
            await Init();
            await using var connection = new SqliteConnection(Constants.DatabasePath);
            await connection.OpenAsync();

            var dropCmd = connection.CreateCommand();
            dropCmd.CommandText = "DROP TABLE IF EXISTS Schedule";
            await dropCmd.ExecuteNonQueryAsync();

            await _exerciseRepository.DropTableAsync();
            _hasBeenInitialized = false;
        }
    }
}